<?php
class SqlBuilder extends Module{
	
	public $sql_parts = null;
	public $sql_final_parts = null;
	public $sql_parts_order = array(
		array('name'=>'join', 'implode'=>"\n"),
		array('name'=>'where', 'implode'=>' AND ', 'pre'=>'WHERE '),
		array('name'=>'having', 'implode'=>' AND ', 'pre'=>'HAVING '),
		array('name'=>'group', 'implode'=>',', 'pre'=>'GROUP BY '),
		array('name'=>'order', 'implode'=>',', 'pre'=>'ORDER BY ')
	);
	public $tables = null;
	public $table_fields = null;
	public $free_fields = null;
	public $limit = null;
	public $sql = null;
	
	public function SqlBuilder(&$application){
		$this->Module($application);
		$this->reset();
	}
	
	public function execute(){
		if($this->sql){
			if(strpos($this->sql, 'SELECT')===0){//return array
				return $this->db->get_rows_q($this->sql);
			}else{
				return $this->db->query($this->sql);
			}
		}else{
			$this->app->err->dump_error('Can\'t execute a query on an invalid SQL');
			return false;
		}
	}
	
	public function quote($str){
		if(is_array($str)){
			$new_arr = array();
			foreach($str as $s){
				$new_arr[] = '`'.$s.'`';
			}
			return $new_arr;
		}else{
			return '`'.$str.'`';
		}
	}
	
	public function reset(){
		$this->sql_parts = array();
		$this->sql_final_parts = array();
		$this->tables = array();
		$this->table_fields = array();
		$this->free_fields = array();
		$this->limit = null;
		$this->sql = null;
	}
	
	public function get(){
		//process fields
		$this->sql_final_parts[] = 'SELECT';
		if($this->table_fields){
			foreach($this->table_fields as $table => $fields){
				$fi = array();
				foreach($fields as $field){
					$fi[] = $table.'.'.$field;
				}
				$this->free_fields[] = implode(', ', $fi);
			}
		}
		$this->sql_final_parts[] = implode(', ', $this->free_fields);
		//process tables
		$this->sql_final_parts[] = 'FROM';
		$this->sql_final_parts[] = implode(', ', $this->tables);
		//process custom order parts
		foreach($this->sql_parts_order as $item){
			if($this->sql_parts[$item['name']]){
				$pre = $item['pre'] ? $item['pre'] : '';
				$this->sql_final_parts[] = $pre.implode($item['implode'], $this->sql_parts[$item['name']]);
			}
		}
		//add limit
		if($this->limit){
			$this->sql_final_parts[] = 'LIMIT '.$this->limit;
		}
		$this->sql = implode(" \n",$this->sql_final_parts);
		return $this->sql;
	}
	
	public function limit($limit){
		$limit = $limit ? $limit : 30;
		$this->limit = $limit;
		return $this;
	}
	
	public function group($field, $table=null){
		$pre = $table ? $table.'.' : '';
		$this->sql_parts['group'][] = $pre.$field;
		return $this;
	}
	
	public function order($field, $table=null, $dir=null){
		$dir = $dir ? $dir : 'ASC';
		$pre = $table ? $table.'.' : '';
		$this->sql_parts['order'][] = $pre.$field.' '.$dir;
		return $this;
	}
	
	public function where($conditions, $table=null){
		$this->conditions('where', $conditions, $table);
		return $this;
	}
	
	public function having($conditions, $table=null){
		$this->add_conditions('having', $conditions, $table);
		return $this;
	}
		
	public function join($field1, $table1=null, $field2=null, $table2=null){
		$this->_add_join('LEFT',$field1, $table1, $field2, $table2);
		return $this;
	}
	
	public function inner_join($field1, $table1=null, $field2=null, $table2=null){
		$this->_add_join('INNER',$field1, $table1, $field2, $table2);
		return $this;
	}
	
	public function select($fields, $table=null){
		//add the table to the table queue
		if($table){
			if(is_array($table)){
				foreach($table as $alias => $name){
					if(is_numeric($alias)){
						$this->_add_table($table);
					}else{
						$this->_add_table($name.' AS '.$alias);
					}
					$table = $alias;//for use later with the fields
				}
			}else{
				$this->_add_table($table);
			}
			if($fields=='*'){
				$this->free_fields[] = $table.'.*';
				return true;
			}
		}
		//add the fields to the fields queue
		if(!is_array($fields)){//simple select
			$this->free_fields[] = $fields;
			return true;
		}else{//associative
			foreach($fields as $alias => $field){
				if($table){
					if(is_numeric($alias)){
						$this->table_fields[$table][] = $this->quote($field);
					}else{
						$this->table_fields[$table][] = $this->quote($field).' AS '.$alias;
					}
				}else{
					if(is_numeric($alias)){
						$this->free_fields[] = $this->quote($field);
					}else{
						$this->free_fields[] = $this->quote($field).' AS '.$alias;
					}
				}
			}
			return true;
		}
	}
	
	public function _add_join($type, $field1, $table1=null, $field2=null, $table2=null){
		if(!$table1 && !$field2 && !$table2){
			$this->_add_part($type.' JOIN '.$field1, 'join');
		}else{
			if(is_array($table1)){
				foreach($table1 as $alias => $name){
					if(!is_numeric($alias)){
						$table1 = $name.' AS '.$alias;
					}
				}
			}
			$this->_add_part($type.' JOIN '.$table1.' ON('.($alias ? $alias : $table1).'.'.$field1.'='.$table2.'.'.$field2.')', 'join');
		}
		$this->_remove_table($alias ? $alias : $table1);
	}
	
	public function conditions($type, $conditions, $table=null){
		if(!is_array($conditions)){
			$this->sql_parts[$type][] = $conditions;
		}else{
			$pre = $table ? $table.'.' : '';
			foreach($conditions as $field => $value){
				if(!is_numeric($field)){
					$this->sql_parts[$type][] = $pre.$field.'='.$value;
				}else{
					$this->sql_parts[$type][] = $value;
				}
			}
		}
	}
	
	public function _add_part($part, $type){
		$this->sql_parts[$type][] = $part;
	}
	
	public function _add_table($table){
		$this->tables[] = $table;
	}
	
	public function _remove_table($table){
		if($this->tables){
			foreach($this->tables as $index => $t){
				if($t==$table){
					unset($this->tables[$index]);
				}
			}
		}
	}
}
?>